package com.jysz.certificate.web.action.pm;

import java.io.IOException;
import java.io.OutputStream;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;

import ognl.Ognl;

import org.apache.commons.lang.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.struts2.convention.annotation.Action;
import org.apache.struts2.convention.annotation.Namespace;
import org.springframework.beans.factory.config.ConfigurableBeanFactory;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;

import com.jysz.certificate.action.AbstractBaseAction;
import com.jysz.certificate.constant.ConfigTypeConstants;
import com.jysz.certificate.constant.ConfigTypeConstants.ConfigTypes;
import com.jysz.certificate.constant.ConfigTypeConstants.PerformanceConfig;
import com.jysz.certificate.constant.OperCodeConstant;
import com.jysz.certificate.persistence.enums.PerformanceAssignStatus;
import com.jysz.certificate.persistence.enums.PostType;
import com.jysz.certificate.persistence.model.mf.Bill;
import com.jysz.certificate.persistence.model.mf.BillOption;
import com.jysz.certificate.persistence.model.pm.PAShopMonthDetail;
import com.jysz.certificate.persistence.model.pm.PerformanceAssign;
import com.jysz.certificate.persistence.model.pm.PerformanceAssignShopDetail;
import com.jysz.certificate.persistence.model.pm.PmUser;
import com.jysz.certificate.persistence.model.pm.PmUserList;
import com.jysz.certificate.persistence.model.system.Financial;
import com.jysz.certificate.persistence.model.system.Organization;
import com.jysz.certificate.persistence.model.system.Shop;
import com.jysz.certificate.service.mf.BillOptionService;
import com.jysz.certificate.service.pm.PAShopMonthDetailService;
import com.jysz.certificate.service.pm.PerformanceAssignService;
import com.jysz.certificate.service.pm.PerformanceAssignShopDetailService;
import com.jysz.certificate.service.pm.PmUserListService;
import com.jysz.certificate.service.pm.PmUserService;
import com.jysz.certificate.service.system.OrganizationService;
import com.jysz.certificate.service.system.ShopService;
import com.jysz.certificate.service.system.UserService;
import com.jysz.certificate.utils.CertificateUtils;
import com.jysz.certificate.utils.DateUtil;
import com.jysz.certificate.utils.PoiUtils;
import com.jysz.webcore.exceptions.WebSystemException;
import com.jysz.webcore.ssh.annotation.PermissionPoint;
import com.jysz.webcore.utils.ReflectionUtils;

/**
 * @author <a href="mailto:javaengine@outlook.com">Kaca Rich</a>
 *
 */
@Controller
@Scope(ConfigurableBeanFactory.SCOPE_PROTOTYPE)
@Namespace("/pm/assign")
public class PerformanceAssignAction extends AbstractBaseAction  {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	@Resource
	private PerformanceAssignService performanceAssignService;
	
	@Resource
	private PerformanceAssignShopDetailService performanceAssignShopDetailService;
	
	@Resource
	private PmUserService pmUserService;
	
	@Resource
	private BillOptionService billOptionService;
	
	@Resource
	private PmUserListService pmUserListService;
	
	@Resource
	private ShopService shopService;
	
	@Resource
	private OrganizationService organizationService;
	@Resource
	private UserService userService;
	@Resource
	private PAShopMonthDetailService paShopMonthDetailService;
	
	private PerformanceAssign search;
	
	private PerformanceAssign entity;
	
	@PermissionPoint(point = OperCodeConstant.M_PM_ASSIGN)
	@Action(INDEX)
	public String index(){
		this.setPage(performanceAssignService.findPage(search, this.getCurrentPage(), this.getPageSize(), this.getUser()));
		
		return INDEX;
	}
	
	@PermissionPoint(point = OperCodeConstant.PM_ASSIGN_ADD)
	@Action(ADD)
	public String add(){
		if(entity == null){
			entity = new PerformanceAssign();
			try {
				entity.setMonthDate(DateUtil.formatDate(DateUtil.getPreMonth(DateUtil.getDate()), "yyyy-MM"));
			} catch (ParseException e) {
				LOG.error("绩效添加，设置初始化月份失败");
			}
			Integer nextDay = CertificateUtils.getIntegerPropertyByKeys(ConfigTypes.PERFORMANCE_CONFIG.getKey(), 
					PerformanceConfig.PM_CONFIRM_FINISH_DAY_QTY.getField());
			nextDay = nextDay == null ? 5 :nextDay;
			entity.setConfirmPlanDate(DateUtil.getNextDay(nextDay));
		}

		return ADD;
	}
	
	@PermissionPoint(point = OperCodeConstant.PM_ASSIGN_ADD)
	@Action(CREATE)
	public String create(){
		try{
			List<Shop> shops = billOptionService.findShopsAtBeforeMonthOfStateCharged(entity.getMonthDate());
			Map<String,Map<Long, Organization>> monthMap = new HashMap<String, Map<Long,Organization>>();
			List<PmUser> genPmUsers = new ArrayList<PmUser>();
			
			entity.setStatus(PerformanceAssignStatus.NOTSTART);
			performanceAssignService.save(entity);
			
			for (Iterator<Shop> iterator = shops.iterator(); iterator.hasNext();) {
				Shop shop =  iterator.next();
				List<String> shopMonths = billOptionService.findShopMonthsAtBeforeMonthOfStateCharged(entity.getMonthDate(), shop);
				
				if(shopMonths != null){
					for (Iterator<String> iterator2 = shopMonths.iterator(); iterator2
							.hasNext();) {
						String shopMonth = iterator2.next();
						
						List<PmUserList> pmUserLists = pmUserListService.findByMonthDate(shopMonth, shop);
						Map<Long, PmUserList> pmUserListMap = this.convertToShopIdKeyMap(pmUserLists);
						if(!pmUserListMap.containsKey(shop.getId())){
							Organization area = shop.getArea();
							
							if(area == null){
								//TODO:不抛出异常，给于用户提示 
								throw new WebSystemException("组织架构区域设置存在问题，请联系管理员！店铺 \"" + shop.getShopName() +"\"");
							}else{
								Map<Long, Organization> areaMap = monthMap.get(monthMap);
								areaMap = areaMap == null ? new HashMap<Long, Organization>() : areaMap;
								if(!areaMap.containsKey(area.getId())){
									//没找 到相关区域的根据区域生成人员名单。
									genPmUsers.add(pmUserService.saveGeneratorPmUser(area, shopMonth, entity));
									areaMap.put(area.getId(), area);
									
									monthMap.put(shopMonth, areaMap);
								}
							}
							
						}
					}
				}
			}
			
			entity.setPmUsers(genPmUsers);
			performanceAssignService.saveGeneratorPaDetails(entity);
			this.setPage(performanceAssignShopDetailService.findPageByPerformanceAssign(null, entity, this.getCurrentPage(), this.getPageSize()));
		}catch(Exception e){
			setFailPopMessage("创建绩效异常:" + e.getMessage());
			LOG.error("创建绩效异常", e);
			return SHOW_MSG_PAGE;
		}
		
		
		setRedirectUrl("edit.do?parent.id=" + entity.getId());
		
//		billOptionService.find
		this.setSuccessPopMessage("绩效创建成功,进入下一步编辑。");
		return SHOW_MSG_PAGE;
	}

	@Action("checkMonthListOfPersons")
	public String checkMonthListOfPersons() {
		Integer day = CertificateUtils.getIntegerPropertyByKeys(ConfigTypes.PERFORMANCE_CONFIG, ConfigTypeConstants.PerformanceConfig.USER_LIST_REMINE_DAY);
		if(day == null){
			this.setFailPopMessage("系统参数人员名单最后提交日期未设置，请设置。");
			this.setJsonMsg();
		}else{
			
			Map<String,Object> data = new HashMap<String, Object>();
			if(performanceAssignService.isExistNoAssignPm()){
				data.put("nopm", true);
				
				this.setJsonMsg("存在还未分配的绩效,请分配之后再新建绩效分配。", false, data);
				return JSON_PAGE;
			}
			List<Shop> shops = billOptionService.findShopsAtBeforeMonthOfStateCharged(entity.getMonthDate());
			if(shops == null || shops.size() < 1){
				data.put("nopm", true);
				this.setJsonMsg("没有可用于计算绩效的发票。。", false, data);
				return JSON_PAGE;
			}else{
				data.put("nopm", false);
			}
			
			Date today = DateUtil.getDate();
			Date monthDate = DateUtil.getDate(entity.getMonthDate(), "yyyy-MM");
			Date pmUserDate = DateUtil.addDays(monthDate, day);
			StringBuilder msgs = new StringBuilder();
			boolean dateOver = false;
			//还未到人员名单最后提交日期
			if(DateUtil.isStartBeforeEndDate(today, pmUserDate)){
				msgs.append("还未到人员名单最后提交期限:" + DateUtil.formatDate(pmUserDate));
				msgs.append("相关参数设置：系统管理->>系统参数<br/>");

			}else{
				dateOver = true;
//				List<PmUser> pmUsers = pmUserService.findAllByMonth(entity.getMonthDate());
			}
			
			Map<String,Map<Long, Organization>> monthMap = new HashMap<String, Map<Long,Organization>>();
			boolean hasNotSubmit = false;
			
			for (Iterator<Shop> iterator = shops.iterator(); iterator.hasNext();) {
				Shop shop =  iterator.next();
				List<String> shopMonths = billOptionService.findShopMonthsAtBeforeMonthOfStateCharged(entity.getMonthDate(), shop);
				
				if(shopMonths != null){
					for (Iterator<String> iterator2 = shopMonths.iterator(); iterator2
							.hasNext();) {
						String shopMonth = iterator2.next();
						
						List<PmUserList> pmUserLists = pmUserListService.findByMonthDate(shopMonth, shop);
						Map<Long, PmUserList> pmUserListMap = this.convertToShopIdKeyMap(pmUserLists);
						if(!pmUserListMap.containsKey(shop.getId())){
							Organization area = shop.getArea();
							
							if(area == null){
								msgs.append("店铺\""+ shop.getShopName() + "\"找不到所属区域。<br/>");
							}else{
								Map<Long, Organization> areaMap = monthMap.get(monthMap);
								areaMap = areaMap == null ? new HashMap<Long, Organization>() : areaMap;
								if(!areaMap.containsKey(area.getId())){
									String areaManager = "";
									List<Organization> ataffs = area.getStaffChilds();
									if(ataffs != null){
										areaManager = ReflectionUtils.convertElementPropertyToString(ataffs, "name", ",");
									}
									
									msgs.append("区域经理：" + areaManager + ",未提交\"" + area.getName() + "\"下\"" + shop.getShopName() + "\"店的" + shopMonth + "月 人员名单。<br/>");
									areaMap.put(area.getId(), area);
									monthMap.put(shopMonth, areaMap);
								}
							}
							
							hasNotSubmit = true;
						}
					}
				}
			
			}
			
			String msg = "";
			if(hasNotSubmit){
				msg = "未提交人员名单区域经理如下:<br/>" + msgs.toString();
			}
			
			data.put("dateOver", dateOver);
			data.put("hasNotSubmit", hasNotSubmit);
			this.setJsonMsg(msg, (dateOver && (!hasNotSubmit)), data);
			
		}
		
		return JSON_PAGE;
	}

	private Map<Long, PmUserList> convertToShopIdKeyMap(List<PmUserList> pmUserLists) {
		Map<Long, PmUserList> result = new HashMap<Long, PmUserList>();
		if(pmUserLists == null){
			return result;
		}
		
		for (Iterator<PmUserList> iterator = pmUserLists.iterator(); iterator.hasNext();) {
			PmUserList pmUserList = iterator.next();
			result.put(pmUserList.getShop().getId(), pmUserList);
		}
		return result;
	}

	@Action(ADD + "_step_1")
	public String add_Step_1() throws ParseException{
		entity = performanceAssignService.find(entity.getId());
		if(search != null){
			StringBuffer sb = new StringBuffer();
			sb.append("index.do?currentPage="+getCurrentPage());

			setRedirectUrl(sb.toString());
		}else{
			setRedirectUrl("index.do?currentPage=" + getCurrentPage());
		}
		if(entity == null){
			return SHOW_MSG_PAGE;
		}
		return EDIT;
	}
	
	
	@PermissionPoint(point = OperCodeConstant.PM_ASSIGN_DELETE)
	@Action(DESTORY)
	public String destory(){
		performanceAssignService.destoryAndPmUsers(entity.getId());
		setSuccessPopMessage("删除数据成功");
		if(search != null){
			StringBuffer sb = new StringBuffer();
			sb.append("index.do?currentPage="+getCurrentPage());
			setRedirectUrl(sb.toString());
		}else{
			setRedirectUrl("index.do?currentPage="+getCurrentPage());
		}
		return SHOW_MSG_PAGE;
	}

	public boolean isCanAssign(PerformanceAssign object){
		Date today = DateUtil.getDate();
		
		return PerformanceAssignStatus.CONFIRM.equals(object.getStatus()) || DateUtil.isStartBeforeEndDate(object.getConfirmPlanDate(), today);
	}
	
	/**
	 * 区域经理的状态和监管科不一样，一个针对所有记录的状态，一个只针对目前区域状态。
	 * @param object
	 * @return
	 */
	public PerformanceAssignStatus getPmStatus(PerformanceAssign object){
		
		if(PostType.Manager.equals(this.getUser()) && PerformanceAssignStatus.NOT_CONFIRM.equals(object.getStatus())){
			//区域经理显示这个区域里的确认状态
			return performanceAssignShopDetailService.getPmStatusByUser(object, this.getUser());
		}
		
		return object.getStatus();
		
	}
	
	@PermissionPoint(point = OperCodeConstant.PM_ASSIGN_CONFIRM_ASSIGN)
	@Action("confirmAssign")
	public String confirmAssign(){ 
		entity = performanceAssignService.find(entity.getId());
		String message ="绩效分配完毕,请等待财务确认后，核发。.。";
		List<BillOption> options = billOptionService.getBillOptionList(entity);
		if(options!=null&&options.size()>0){
			for (BillOption option : options) {
				Bill bill = option.getBills().get(0);
				message = "该管理费明细下存在一条发票号:"+bill.getBillNum()+"的记录在"+option.getMonth()
						+"月份被分配过，分配的金融机构为 "+option.getFinancial().getName()
						+"，店铺为"+option.getShop().getShopName()+" ，金额为"+option.getAmount();
			}
		}else{
			entity.setStatus(PerformanceAssignStatus.ASSIGN);
			entity.setConfirmAssignDate(DateUtil.getDate());
			entity.setConfirmAssignUser(this.getUser());
			
			performanceAssignService.saveConfirmAssign(entity);
		}
		setSuccessPopMessage(message);
		setRedirectUrl("index.do?currentPage=" + getCurrentPage());
		return SHOW_MSG_PAGE;
	}
	
	@PermissionPoint(point = OperCodeConstant.PM_ASSIGN_AREA_CONFIRM)
	@Action("areaConfirm")
	public String areaConfirm(){
		//所管理的区域所有店铺 确认工作,店铺详情确认，需增加区域经理确认状态，全部为确认，绩效状态才改变
		entity = performanceAssignService.find(entity.getId());
//		entity.setStatus(PerformanceAssignStatus.CONFIRM);
		performanceAssignService.saveAreaConfirm(entity, this.getUser());
		
		setSuccessPopMessage("确认结束,等待监管科审核后提交财务。。.");
		setRedirectUrl("index.do?currentPage=" + getCurrentPage());
		return SHOW_MSG_PAGE;
	}
	
	@PermissionPoint(point = OperCodeConstant.PM_ASSIGN_RELEASE_CONFIRM)
	@Action("releaseConfirm")
	public String releaseConfirm(){ 
		//监管科发布确认
		entity = performanceAssignService.find(entity.getId());
		entity.setStatus(PerformanceAssignStatus.NOT_CONFIRM);
		entity.setReleaseConfirmDate(DateUtil.getDate());
		entity.setReleaseConfirmUser(this.getUser());
		
		performanceAssignService.save(entity);
		
		setSuccessPopMessage("绩效分配发布完毕,请等待最后确认日期.。。");
		setRedirectUrl("index.do?currentPage=" + getCurrentPage());
		return SHOW_MSG_PAGE;
	}
	
	@PermissionPoint(point = OperCodeConstant.PM_ASSIGN_PM_CONFIRM)
	@Action("pmConfirm")
	public String pmConfirm(){
		entity = performanceAssignService.find(entity.getId());
		entity.setPmConfirmDate(DateUtil.getDate());
		entity.setPmConfirmUser(this.getUser());
		
		performanceAssignService.savePmConfirm(entity);
		setSuccessPopMessage("确认完毕,请等待绩效岗发放金额.相关绩效可以到绩效查询里查询。");
		setRedirectUrl("index.do?currentPage=" + getCurrentPage());
		return SHOW_MSG_PAGE;
	}
	
	@SuppressWarnings("unchecked")
	@PermissionPoint(point = OperCodeConstant.PM_ASSIGN_PM_EXPORT)
	@Action("export")
	public String export(){
		//FIXME:重构优化 
		entity = performanceAssignService.find(entity.getId());
	    
		OutputStream out = null;
		try {
	        Map<String, Object> context = new HashMap<String, Object>();
	        context.put("paa", this);
			String[] headerStarts = {"序号", "店号", "签约销售店", "驻店顾问", "区域经理","当月入库总计","上月结存总计"};
			String[] headerEnds = {"管理费总计(元)","驻店员提成(元)","区域经理提成(元)","区域经理提成(元)"};
	        String[] columnHeaders = {"#index", "performanceAssignShopDetail.shop.shopMark", 
	        		"performanceAssignShopDetail.shop.shopName", "assignUserNames", "performanceAssignShopDetail.areaUserNames", "currenMonthInQty","preMonthInQty"};
	        String[] columnEnds = {"payinAmount","totalAmount","areaTotalAmount","''"};
			List<String> headers = new ArrayList<String>();
		    List<Financial> financials = paShopMonthDetailService.findFinancialsByMonthPm(entity);
			headers.addAll(Arrays.asList(headerStarts));
			
	        if(financials != null && financials.size() > 0){
	        	headers.addAll(ReflectionUtils.convertElementPropertyToList(financials, "name"));
	        }
	        headers.addAll(Arrays.asList(headerEnds));
	        
			HttpServletResponse response = this.getResponse();
		    response.setContentType("octets/stream");
		    response.addHeader("Content-Disposition",
		                "attachment;filename=" + toUtf8Encode("绩效详情_") + entity.getMonthDate()+".xls");
		    

		    List<String> months = paShopMonthDetailService.findMonthsByMonthPm(entity);
		    
			out = this.getResponse().getOutputStream();
			
			HSSFWorkbook workbook = new HSSFWorkbook();
//			HSSFFormulaEvaluator eval=new HSSFFormulaEvaluator(workbook);  
	        HSSFSheet sheet = PoiUtils.createSheet(entity.getMonthDate(), workbook);
	        int rowIndex = 0;
			HSSFCellStyle headerStyle = PoiUtils.createHeaderStyle(workbook);
			HSSFCellStyle headerMonthStyle = PoiUtils.createHeaderStyle(workbook);
			headerMonthStyle.setFillForegroundColor(HSSFColor.WHITE.index);
			
			HSSFCellStyle totalCellStyle = PoiUtils.createHeaderStyle(workbook);
			totalCellStyle.setFillForegroundColor(HSSFColor.WHITE.index);
			
	        HSSFCellStyle contentStyle = PoiUtils.createContentCellStyle(workbook);
	        int monthBeginRowIndex = rowIndex;
	        
	        List<String> columnList = this.converToOgnlExpresss(columnHeaders, financials);
	        
	        columnList.addAll(Arrays.asList(columnEnds));
	        
	        Object[] columns = columnList.toArray();
		    for (Iterator<String> iterator = months.iterator(); iterator.hasNext();) {
				String month = iterator.next();
				List<PAShopMonthDetail> shopMonthDetails = paShopMonthDetailService.findByMonth(month, entity);
				
				if(shopMonthDetails == null || shopMonthDetails.isEmpty()){
					continue;
				}
				
				// 产生表格标题行
				HSSFRow row = sheet.createRow(rowIndex);
				
			    HSSFCell cell = row.createCell(0);
			    cell.setCellStyle(headerMonthStyle);
			    HSSFRichTextString text = new HSSFRichTextString(month + "月份合格证监管提成发放一览表");
			    
			    cell.setCellValue(text);
			    sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, headers.size() - 1 )); 
			    
				row = sheet.createRow( ++rowIndex );
		        this.generatorHeaders(row, headerStyle, headers.toArray());
		        
		        rowIndex++;
		        monthBeginRowIndex = rowIndex;
	
				int index = 0;
				Organization preOrg = null;
				double areaTotalAmount = 0;
				int beginRowIndex = rowIndex;
				int endRowIndex = rowIndex;
		        for (Iterator<PAShopMonthDetail> iterator2 = shopMonthDetails.iterator(); iterator2
						.hasNext();) {
		        	index ++;
					PAShopMonthDetail paShopMonthDetail = iterator2.next();
					Organization org = paShopMonthDetail.getPerformanceAssignShopDetail().getShop().getArea();
					if(preOrg != null && org.getId() == preOrg.getId()){
						areaTotalAmount += paShopMonthDetail.getAreaTotalAmount();
						endRowIndex = rowIndex;
					}else{
						beginRowIndex = rowIndex;
						areaTotalAmount = paShopMonthDetail.getAreaTotalAmount();
					}
					
					preOrg = org;
					
					if(!iterator2.hasNext()){
						endRowIndex = rowIndex;
					}
					
					row = sheet.createRow(rowIndex);
					context.put("index", index);
					 for (int i = 0; i < columns.length; i++) {
						 cell = row.createCell(i);
			             cell.setCellStyle(contentStyle);
			         	
						 Object obj = Ognl.getValue(columns[i].toString(), context, paShopMonthDetail);
						 
						 String textValue = obj != null ? obj.toString() : "";
						 //最后列区域经理绩效总计
						 if(i == columns.length - 1){
							 textValue = String.valueOf(areaTotalAmount);
							 sheet.addMergedRegion(new CellRangeAddress(beginRowIndex, endRowIndex, headers.size() - 1, headers.size() - 1 )); 
						 }
							
				         if (textValue != null) {
//			                 Pattern p = Pattern.compile("^//d+(//.//d+)?{1}");
//			                 Matcher matcher = p.matcher(textValue);
//			                 if (matcher.matches()) {
				                 // 是数字当作double处理
//				                 cell.setCellValue(Double.parseDouble(textValue));
				          	 if( NumberUtils.isNumber(textValue)){
				                 // 是数字当作double处理
				        		 cell.setCellValue(NumberUtils.toDouble(textValue));
			                 } else {
				                 HSSFRichTextString richString = new HSSFRichTextString(
				                                    textValue);
				                 HSSFFont font3 = PoiUtils.createTextFont(workbook);
				                 richString.applyFont(font3);
				                 cell.setCellValue(richString);
			                 }
				         }
						
					 }
					 
					rowIndex++;
				}
		        
			    //添加合计 
			    row = sheet.createRow(rowIndex);
			    cell = row.createCell(0);
			 
	            HSSFRichTextString richString = new HSSFRichTextString("合计");
				HSSFFont font3 = PoiUtils.createTextFont(workbook);
				font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
				font3.setColor(HSSFColor.BLACK.index);
				richString.applyFont(font3);
				
				cell.setCellValue(richString);
				sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 4 )); 
				cell.setCellStyle(totalCellStyle);
				
			    cell = row.createCell(5, HSSFCell.CELL_TYPE_FORMULA);
			    cell.setCellValue(richString);
			    cell.setCellFormula("SUM(" + PoiUtils.getColLetter(cell.getColumnIndex()) + (monthBeginRowIndex+1) + ":" + PoiUtils.getColLetter(cell.getColumnIndex()) + cell.getRowIndex() + ")");
			    cell.setCellStyle(totalCellStyle);
			   
			    cell = row.createCell(6, HSSFCell.CELL_TYPE_FORMULA);
			    cell.setCellStyle(totalCellStyle);
			    cell.setCellValue(richString);
			    cell.setCellStyle(totalCellStyle);
			    cell.setCellFormula("SUM(" + PoiUtils.getColLetter(cell.getColumnIndex()) + (monthBeginRowIndex+1) + ":" + PoiUtils.getColLetter(cell.getColumnIndex()) + cell.getRowIndex() + ")");
			    
			    if(financials != null){
				    for (int i = 7; i < financials.size() + 7 + headerEnds.length; i++) {
				    	cell = row.createCell(i, HSSFCell.CELL_TYPE_FORMULA);
				    	
				    	cell.setCellFormula("SUM(" + PoiUtils.getColLetter(cell.getColumnIndex()) + (monthBeginRowIndex+1) + ":" + PoiUtils.getColLetter(cell.getColumnIndex()) + cell.getRowIndex() + ")");
				    	cell.setCellStyle(totalCellStyle);
				    }
			    }
			    rowIndex++;
			    
			    row = sheet.createRow(rowIndex);
			    cell = row.createCell(0);
	            richString = new HSSFRichTextString("管理费统计");
				richString.applyFont(font3);
				cell.setCellValue(richString);
				cell.setCellStyle(totalCellStyle);
				
				cell = row.createCell(1);
				cell.setCellStyle(totalCellStyle);
				cell = row.createCell(2);
				cell.setCellStyle(totalCellStyle);
				sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 2 )); 
				
			    cell = row.createCell(3);
	            richString = new HSSFRichTextString("提成总计");
				richString.applyFont(font3);
				cell.setCellValue(richString);
				cell.setCellStyle(totalCellStyle);
				
				cell = row.createCell(4);
				cell.setCellStyle(totalCellStyle);
				sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 3, 4 )); 
				
				rowIndex++;
				
			    row = sheet.createRow(rowIndex);
			    cell = row.createCell(0, HSSFCell.CELL_TYPE_FORMULA);
			    int financialsSize = financials != null ? financials.size() : 0;
		    	if(financialsSize > 0){
		    		cell.setCellFormula("SUM(" + PoiUtils.getColLetter(7) + (rowIndex - 1) + ":" + PoiUtils.getColLetter(7 + financialsSize) + (rowIndex -1) + ")");
		    	}
		    	cell.setCellStyle(totalCellStyle);
		    	
				cell = row.createCell(1);
				cell.setCellStyle(totalCellStyle);
				cell = row.createCell(2);
				cell.setCellStyle(totalCellStyle);
				sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 2 )); 
				
			    cell = row.createCell(3, HSSFCell.CELL_TYPE_FORMULA);
			    cell.setCellFormula("SUM(" + PoiUtils.getColLetter(columns.length-3) + (rowIndex -1) + "," + PoiUtils.getColLetter(columns.length -1) + (rowIndex -1) + ")");
		    	cell.setCellStyle(totalCellStyle);
		    	
				cell = row.createCell(4);
				cell.setCellStyle(totalCellStyle);
				sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 3, 4 )); 
				
				rowIndex++;
			}
		    
		    headerMonthStyle = PoiUtils.createHeaderStyle(workbook);
		    headerMonthStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		    headerMonthStyle.setFillForegroundColor(HSSFColor.WHITE.index);
		    
		    //按季度收，按年度收
//	        String[] shopColumnHeaders1 = {"#index", "shop.shopMark", 
//	        		"shop.shopName"};//, "assignUserNames", "areaUserNames", "currenMonthInQty","preMonthInQty"
//	        String[] columnEnds1 = {"payinAmount","totalAmount","areaTotalAmount","''"};
//		    financials = paShopMonthDetailService.findFinancialsByManyMonthPm(entity);
//		    String var = "shopDetail";
//		    for (Iterator<Financial> iterator = financials.iterator(); iterator.hasNext();) {
//		    	//按季度
//		    	Financial financial = iterator.next();
//		        months = paShopMonthDetailService.findFinancialMonthsByMonthPm(entity, financial);
//		        
//		        context.put("months", months);
//		        //生成OGNL表达式
//		        columnList = this.converManyMonthToOgnlExpresss(shopColumnHeaders1, financial , months , var);
//		        
//		        columnList.addAll(Arrays.asList(columnEnds1));
//		        
//		        Object[] columns1 = columnList.toArray();
//		        
//		        List<PerformanceAssignShopDetail> shopDetails = paShopMonthDetailService.findByFinancialMonths(financial, months, entity);
//		        
//		        HSSFRow row = sheet.createRow(rowIndex);
//			    HSSFCell cell = row.createCell(0);
//			    cell.setCellStyle(headerMonthStyle);
//			    HSSFRichTextString text = new HSSFRichTextString(financial.getName() + "(" + StringUtils.join(months, "、") + ")");
//			    
//			    cell.setCellValue(text);
//			    sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, headerStarts.length + headerEnds.length + months.size() - 1)); 
//			    headers = new ArrayList<String>();
//				headers.addAll(Arrays.asList(headerStarts));
//		        headers.addAll(months);
//		        headers.addAll(Arrays.asList(headerEnds));
//		        
//				row = sheet.createRow( ++rowIndex );
//		        this.generatorHeaders(row, headerStyle, headers.toArray());
//		        
//		        rowIndex++;
//		        int shopBeginRowIndex = rowIndex;
//	
//				int index = 0;
//				Organization preOrg = null;
//				double areaTotalAmount = 0;
//				int beginRowIndex = rowIndex;
//				int endRowIndex = rowIndex;
//			    
//				//多月份店铺迭代。。
//		        for (Iterator<PerformanceAssignShopDetail> iterator2 = shopDetails.iterator(); iterator2
//						.hasNext();) {
//		        	index ++;
//		        	PerformanceAssignShopDetail shopDetail = iterator2.next();
//					Organization org = shopDetail.getShop().getArea();
//					if(preOrg != null && org.getId() == preOrg.getId()){
//						areaTotalAmount += shopDetail.getAreaTotalAmount();
//						endRowIndex = rowIndex;
//					}else{
//						beginRowIndex = rowIndex;
//						areaTotalAmount = shopDetail.getAreaTotalAmount();
//					}
//					
//					preOrg = org;
//					
//					if(!iterator2.hasNext()){
//						endRowIndex = rowIndex;
//					}
//					
//					row = sheet.createRow(rowIndex);
//					context.put("index", index);
//					context.put(var, shopDetail);
//					 for (int i = 0; i < columns1.length; i++) {
//						 cell = row.createCell(i);
//			             cell.setCellStyle(contentStyle);
//			         	
//						 Object obj = Ognl.getValue(columns1[i].toString(), context, shopDetail);
//						 
//						 String textValue = obj != null ? obj.toString() : "";
//						 //最后列区域经理绩效总计
//						 if(i == columns1.length - 1){
//							 textValue = String.valueOf(areaTotalAmount);
//							 sheet.addMergedRegion(new CellRangeAddress(beginRowIndex, endRowIndex, headers.size() - 1, headers.size() - 1 )); 
//						 }
//							
//				         if (textValue != null) {
////			                 Pattern p = Pattern.compile("^//d+(//.//d+)?{1}");
////			                 Matcher matcher = p.matcher(textValue);
////			                 if (matcher.matches()) {
//				                 // 是数字当作double处理
////				                 cell.setCellValue(Double.parseDouble(textValue));
//				          	 if( NumberUtils.isNumber(textValue)){
//				                 // 是数字当作double处理
//				        		 cell.setCellValue(NumberUtils.toDouble(textValue));
//			                 } else {
//				                 HSSFRichTextString richString = new HSSFRichTextString(
//				                                    textValue);
//				                 HSSFFont font3 = PoiUtils.createTextFont(workbook);
//				                 richString.applyFont(font3);
//				                 cell.setCellValue(richString);
//			                 }
//				         }
//						
//					 }
//					 
//					rowIndex++;
//				}
//			}
		    //强制计算公式 
//		    eval.evaluateAllFormulaCells(workbook);
//		    sheet.setForceFormulaRecalculation(true);
			try {
				workbook.write(out);
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			LOG.error("导出绩效出错。。。", e);
		}
//		
		return null;
	}

	/**
	 * 转换成ognl表达式 
	 * 
	 * @param columnHeaders
	 * @param financials
	 * @return
	 */
	public List<String> converToOgnlExpresss(String[] columnHeaders,
			List<Financial> financials) {
		List<String> columnList = new ArrayList<String>();
		  
		columnList.addAll(Arrays.asList(columnHeaders));
		if(financials != null && financials.size() > 0){
			for (Iterator<Financial> iterator2 = financials.iterator(); iterator2
					.hasNext();) {
				Financial financial = iterator2.next();
				
				columnList.add("getAmountByFinancialId(" + financial.getId() + ")");
			}
		}
		return columnList;
	}
	
	/**
	 * 转换成ognl表达式 
	 * 
	 * @param columnHeaders
	 * @param financials
	 * @return
	 */
//	private List<String> converManyMonthToOgnlExpresss(
//			String[] shopColumnHeaders1, Financial financial,
//			List<String> months, String var){
//		List<String> columnList = new ArrayList<String>();
//		//"currenMonthInQty","preMonthInQty"
////		, "assignUserNames", "areaUserNames"
//		columnList.addAll(Arrays.asList(shopColumnHeaders1));
//		columnList.add("#paa.getAssignUserNamesByFinancialIdMonths("+ financial.getId() + ", #months, #" + var + ")");
//		columnList.add("areaUserNames");
//		columnList.add("#paa.getCurrenMonthInQtyByFinancialIdMonths("+ financial.getId() + ", #months, #" + var + ")");
//		columnList.add("#paa.getPreMonthInQtyByFinancialIdMonths("+ financial.getId() + ", #months, #" + var + ")");
//		if(months != null && months.size() > 0){
//			for (Iterator<String> iterator2 = months.iterator(); iterator2
//					.hasNext();) {
//				String month = iterator2.next();
//				
//				columnList.add("#paa.getAmountByFinancialIdMonth(" + financial.getId() + ",'" + month + "',#" + var + ")");
//			}
//		}
//		return columnList;
//	}  
//
//	public int getCurrenMonthInQtyByFinancialIdMonths(Long financialId, List<String> months, PerformanceAssignShopDetail shopDetail){
//		
//		return 0;
//	}
//	
//	public int getPreMonthInQtyByFinancialIdMonths(Long financialId, List<String> months, PerformanceAssignShopDetail shopDetail){
//		return 0;
//	}
//	
//	public double getAmountByFinancialIdMonth(Long financialId, String month, PerformanceAssignShopDetail shopDetail){
//		
////		return paShopMonthDetailService.getAmountByFinancialIdMonth(financialId, month, shopDetail);
//		return 0;
//	}
	
	public String getAssignUserNamesByFinancialIdMonths(Long financialId, List<String> months, PerformanceAssignShopDetail shopDetail){
		return paShopMonthDetailService.getAssignUserNamesByFinancialIdMonths(financialId, months, shopDetail);
	}
	@SuppressWarnings("deprecation")
	public void generatorHeaders(HSSFRow row, HSSFCellStyle headerStyle, Object[] headers) {

		for (short i = 0; i < headers.length; i++) {
		    HSSFCell cell = row.createCell(i);
		    cell.setCellStyle(headerStyle);
		    HSSFRichTextString text = new HSSFRichTextString(headers[i].toString());
		    cell.setCellValue(text);
		}
	}
	
	public PerformanceAssign getSearch() {
		return search;
	}

	public void setSearch(PerformanceAssign search) {
		this.search = search;
	}

	public PerformanceAssign getEntity() {
		return entity;
	}

	public void setEntity(PerformanceAssign entity) {
		this.entity = entity;
	}

	
}
